package studentsinfomanagement.dao;

import studentsinfomanagement.bean.SC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 选课数据类

 */
public class SCDAO extends BaseDAO{

    private static SCDAO scd;

    /**
     * 单例模式
     */
    private SCDAO(){}

    /**
     * 获取SCDAO类的实例
     * @return 实例
     */
    public static SCDAO getInstance(){
        if(scd == null){
            scd = new SCDAO();
        }
        return scd;
    }

    /**
     * 根据学生学号获取选课信息
     * @return
     */
    public List<SC> getSelectedCourseBySno(String sno){
        //准备sql查询语句
        String sql = "select * from sc where sno=?";
        //准备参数
        String[] param = { sno };
        //调用数据库工具类的查询方法，执行查询语句
        rs = db.executeQuery(sql, param);
        List<SC> csList = new ArrayList<>();
        try {
            //将查询结果存入集合并返回
            while (rs.next()){
                SC sc = new SC();
                sc.setSno(sno);
                sc.setCno(rs.getString("cno"));
                sc.setGrade(rs.getFloat("grade"));
                csList.add(sc);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            destroy();
        }
        return csList;
    }

    /**
     * 根据课程号获取全部选修该课程的学生信息
     * @param cno 课程号
     * @return 选修该课程的全部选课信息
     */
    public List<SC> getStudentsByCno(String cno){
        //准备sql查询语句
        String sql = "select * from sc where cno=?";
        //准备参数
        String[] param = { cno };
        //调用数据库工具类的查询方法，执行查询语句
        rs = db.executeQuery(sql, param);
        List<SC> csList = new ArrayList<>();
        try {
            //将查询结果存入集合并返回
            while (rs.next()){
                SC sc = new SC();
                sc.setSno(rs.getString("sno"));
                sc.setCno(cno);
                sc.setGrade(rs.getFloat("grade"));
                csList.add(sc);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            destroy();
        }
        return csList;
    }

    public Float getGrade(String sno, String cno){
        String sql = "select * from sc where sno=? and cno=?";
        String[] param = {sno, cno};
        ResultSet rs = db.executeQuery(sql,param);
        Float result = -1f;
        try {
            if (rs.next()){
                result = rs.getFloat("grade");
            }
        }catch (SQLException e){
            result = -1f;
        }
        return result;
    }

    /**
     * 学生选修课程
     * @param sno 学号
     * @param cno 课程号
     */
    public boolean selectCourse(String sno, String cno){//学生选课 未完成
        boolean result = false;
        //学号，课程号，学工号不能为空
        if (sno == null || cno == null ) {
            return result;
        }

        //检查课程是否存在
        CourseDAO courseDAO = CourseDAO.getInstance();
        if(courseDAO.getCourseByCno(cno) != null){//查询到课程
            // 执行插入语句
            String sql = "insert into sc(sno,cno) values(?,?)";
            String[] param = { sno, cno};
            if (db.executeUpdate(sql, param) == 1) {
                result = true;
            }
            //关闭resultSet
            destroy();
        }else{
            System.out.println("未查询到课程："+cno);
        }
        System.out.println("result:"+result);
        return result;
    }

    /**
     * 更新选课信息(成绩）
     * @param sc 选课信息对象
     * @return 更新结果
     */
    public boolean update(SC sc){
        boolean result = false;
        if(sc == null)
            return result;
        String sql = "update sc set grade=? where sno=? and cno=?";
        Float grade = sc.getGrade();
        String[] param = {grade.toString(), sc.getSno(), sc.getCno()};
        int rowCount = db.executeUpdate(sql, param);
        if(rowCount == 1)
            result = true;
        destroy();
        return result;
    }
    /**
     * 删除选课信息
     * @param sc 选课对象
     * @return 操作结果
     */
    public boolean delete(SC sc) {
        boolean result = false;
        if (sc == null) {
            return result;
        }
        String sql = "delete from sc where sno=? and cno=?";
        String[] param = { sc.getSno(), sc.getCno()};
        int rowCount = db.executeUpdate(sql, param);
        if (rowCount == 1) {
            result = true;
        }
        destroy();
        return result;
    }

}
